package com.geline.cloud.util;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.date.LocalDateTimeUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.toolkit.sql.StringEscape;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author: mengxin
 * @date: 2023/12/9 10:35
 */
public class DynamicSqlUtil {

    /**
     * 解析动态SQL+传入参数，生成新的可执行SQL
     * @param dynamicSql
     * @param map
     * @return
     */
    public static String parseSQL(String dynamicSql, Map<String, Object> map){
        //$xxx+表示检验必传参数
        List<String> requiredKeys = parseRequiredKeyList(dynamicSql);
        if(requiredKeys!=null && requiredKeys.size()>0){
            for(String key : requiredKeys){
                if(!map.containsKey(key)){
                    throw new RuntimeException(String.format("%s is required !", key));
                }
            }
        }
        String newSql = dynamicSql;
        for(String key : map.keySet()){
            Object value = map.get(key);
            if(value==null){
                continue;
            }
            if(value instanceof Collection){
                Collection list = (Collection)value;
                if(list.size()>0){
                    Object first = list.toArray()[0];
                    String values = null;
                    if(first instanceof CharSequence){
                        List<CharSequence> newList = (List<CharSequence>) list.stream().map(row -> escapeString(row.toString())).collect(Collectors.toList());
                        values = "'"+StrUtil.join("','", newList)+"'";
                    }else {
                        values = StrUtil.join(",", list);
                    }
                    newSql = newSql.replace("${"+key+"+}", values);
                    newSql = newSql.replace("${"+key+"}", values);
                    newSql = newSql.replace("#{"+key+"+}", values);
                    newSql = newSql.replace("#{"+key+"}", values);
                }
            }else if(value.getClass().isArray()){
                List list = Arrays.asList((Object[])value);
                if(list.size()>0){
                    Object first = list.toArray()[0];
                    String values = null;
                    if(first instanceof CharSequence){
                        List<CharSequence> newList = (List<CharSequence>) list.stream().map(row -> escapeString(row.toString())).collect(Collectors.toList());
                        values = "'"+StrUtil.join("','", newList)+"'";
                    }else {
                        values = StrUtil.join(",", list);
                    }
                    newSql = newSql.replace("${"+key+"+}", values);
                    newSql = newSql.replace("${"+key+"}", values);
                    newSql = newSql.replace("#{"+key+"+}", values);
                    newSql = newSql.replace("#{"+key+"}", values);
                }
            }else if(value instanceof CharSequence){
                // [and id in(#{idList})]  --> and id in('1','2','3')
                newSql = newSql.replace("${"+key+"+}", escapeString(value.toString()));
                newSql = newSql.replace("${"+key+"}", escapeString(value.toString()));
                String wrap = StrUtil.wrap(escapeString(value.toString()), "'");
                newSql = newSql.replace("#{"+key+"+}", wrap);
                newSql = newSql.replace("#{"+key+"}", wrap);
            }else {
                // [and id in(#{idList})]  --> and id in(1,2,3)
                newSql = newSql.replace("${"+key+"+}", String.valueOf(value));
                newSql = newSql.replace("${"+key+"}", String.valueOf(value));
                newSql = newSql.replace("#{"+key+"+}", String.valueOf(value));
                newSql = newSql.replace("#{"+key+"}", String.valueOf(value));
            }
        }

        //截取动态条件 [name=${name}]
        List<String> list = ReUtil.findAllGroup0("\\[([^\\]]+)\\]", newSql);
        if(list!=null && list.size()>0){
            for(String subStr : list){
                if(subStr.contains("${") || subStr.contains("#{")){
                    //清除有变量条件
                    newSql = newSql.replace(subStr, "");
                }else {
                    //去除条件的[]
                    newSql = newSql.replace(subStr, subStr.substring(1, subStr.length()-1));
                }
            }
        }
        //替换多个空格为一个空格
        newSql = newSql.replaceAll("\\s{2,}", " ");
        return newSql;
    }

    /**
     * 解析获取必填参数变量 ${key+} #{key+}
     * @param dynamicSql
     * @return [key1, key2]
     */
    public static List<String> parseRequiredKeyList(String dynamicSql){
        List<String> requiredKeys = ReUtil.findAllGroup0("\\{([^\\+]+)\\+\\}", dynamicSql);
        if(requiredKeys!=null && requiredKeys.size()>0){
            return requiredKeys.stream().map(row -> row.substring(1, row.length() - 2)).collect(Collectors.toList());
        }
        return new ArrayList<>();
    }

    public static String escapeString(String value){
        return value.replace("'", "''");
    }

    public static String quotaMark(Object obj) {
        if(obj instanceof String) {
            String srcStr = String.valueOf(obj);
            return StringEscape.escapeString(srcStr);
        } else if(obj instanceof Date) {
            return StringEscape.escapeString(DateUtil.formatDate((Date)obj));
        } else if(obj instanceof LocalDate) {
            LocalDate localDate = (LocalDate)obj;
            return StringEscape.escapeString(LocalDateTimeUtil.formatNormal(localDate));
        } else if(obj instanceof LocalDateTime) {
            return StringEscape.escapeString(DateUtil.formatLocalDateTime((LocalDateTime)obj));
        } else {
            return String.valueOf(obj);
        }
    }

    public static String quotaMark(Class type, Object value) {
        if(type == String.class) {
            String srcStr = String.valueOf(value);
            return StringEscape.escapeString(srcStr);
        } else if(type == Character.class) {
            return StringEscape.escapeString(DateUtil.formatDate((Date)value));
        } else if(type == Date.class) {
            return StringEscape.escapeString(DateUtil.formatDate((Date)value));
        } else if(type == LocalDate.class) {
            LocalDate localDate = (LocalDate)value;
            return StringEscape.escapeString(LocalDateTimeUtil.formatNormal(localDate));
        } else if(type == LocalDateTime.class) {
            return StringEscape.escapeString(DateUtil.formatLocalDateTime((LocalDateTime)value));
        } else {
            return String.valueOf(value);
        }
    }

    public static void main(String[] args) {
        String sql = "select * from table where 1=1[ and name like '%${name}%'][ and id in(#{idList})]";
        Map<String, Object> map = new HashMap<>();
        String newSql1 = parseSQL(sql, map);
        //select * from table where 1=1
        System.out.println(newSql1);

        map.put("name", "系統");
        String newSql2 = parseSQL(sql, map);
        //select * from table where 1=1 and name like '%系統%'
        System.out.println(newSql2);

        map.put("idList", Arrays.asList("aa", "bb"));
        String newSql3 = parseSQL(sql, map);
        //select * from table where 1=1 and name like '%系統%' and id in('aa','bb')
        System.out.println(newSql3);

        Integer[] idArray = new Integer[]{1, 5};
        map.put("idList", idArray);
        String newSql4 = parseSQL(sql, map);
        //select * from table where 1=1 and name like '%系統%' and id in(1,5)
        System.out.println(newSql4);

        map.put("idList", new ArrayList<>());
        String newSql5 = parseSQL(sql, map);
        //select * from table where 1=1 and name like '%系統%'
        System.out.println(newSql5);
    }
}
